package net.wantongtech.xa.uploadbasicinfo.dao;

import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.GenericResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.CardInfoOpt;
import com.wtkj.vo.dzzf.base.kfwd.PacketCaptureCardOpt;
import org.javatuples.Pair;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * 顺延包年有效期DAO
 */
@Repository
public class PostPoneAnnualTimeDao extends BaseDataTemplate {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public List<Pair<Integer, String>> queryAllSelfAnnualCardInfo() {
        final String sql =
                "select distinct (a.cardnetid || a.ecardno)\n" +
                        " from ac_cardinfoopt a\n" +
                        "where a.opttype in  ('1200','1223')\n" +
                        "  and a.state = 1\n" +
                        "  and a.cardnetid = 6101\n" +
                        "  and a.packetcaptureendon >= date '2020-02-17'\n" +
                        "  and not exists\n" +
                        "(select 1\n" +
                        "         from ac_cardinfoopt t\n" +
                        "        where t.state = 1\n" +
                        "          and t.opttype = '1080'\n" +
                        "          and t.ecardno = a.ecardno\n" +
                        "          and (t.opton < date '2020-02-17' or t.opton > date '2020-05-05'))";
        return jdbcTemplate.query(sql, (rs, i) -> {
            String cardNo = rs.getString(1);
            return Pair.with(Integer.valueOf(cardNo.substring(0, 4)), cardNo.substring(4));
        });
    }


    public List<Pair<Integer, String>> queryAllBankAnnualCardInfo() {
        final String sql =
                "select distinct (a.cardnetid || a.ecardno)\n" +
                        "  from ac_packetcapturecardopt a\n" +
                        " where a.opttype in ('1200', '1223')\n" +
                        "   and a.state = 1\n" +
                        "   and a.cardnetid <> 6101\n" +
                        "   and a.packetcaptureendon >= date '2020-02-17'\n" +
                        "   and not exists\n" +
                        " (select 1\n" +
                        "          from ac_packetcapturecardopt t\n" +
                        "         where t.state = 1\n" +
                        "           and t.opttype = '1080'\n" +
                        "           and t.ecardno = a.ecardno\n" +
                        "           and (t.opton < date '2020-02-17' or t.opton > date '2020-05-05'))";
        return jdbcTemplate.query(sql, (rs, i) -> {
            String cardNo = rs.getString(1);
            return Pair.with(Integer.valueOf(cardNo.substring(0, 4)), cardNo.substring(4));
        });
    }

    public List<CardInfoOpt> queryAnnualCardInfoOpt(String eCardNo) {
        final String sql =
                "select *\n" +
                        "  from ac_cardinfoopt a\n" +
                        " where a.state = 1\n" +
                        "   and a.opttype in ('1200', '1080', '1223')\n" +
                        "   and a.ecardno = ?\n" +
                        " order by a.opton desc";
        return jdbcTemplate.query(sql, GenericResultSetExtractor.getInstance(CardInfoOpt.class), eCardNo);
    }


    public List<PacketCaptureCardOpt> queryBankAnnualCardInfoOpt(String eCardNo) {
        final String sql =
                "select *\n" +
                        "  from ac_packetcapturecardopt a\n" +
                        " where a.state = 1\n" +
                        "   and a.opttype in ('1200', '1080', '1210', '1223')\n" +
                        "   and a.ecardno = ?\n" +
                        " order by a.opton desc";
        return jdbcTemplate.query(sql, GenericResultSetExtractor.getInstance(PacketCaptureCardOpt.class), eCardNo);
    }
}
